﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DGSFramework;
using System.Web.Security;
using NPOI.SS.UserModel;
using System.Text.RegularExpressions;
using NPOI.HSSF.UserModel;
using System.IO;

namespace Framework.WebmasterTool
{
    public partial class Reports : System.Web.UI.Page
    {
        DGSFrameworkEntities entity = new DGSFrameworkEntities();

        public string GridViewSortExpression
        {
            get { return ViewState["GridViewSortExpression"] == null ? "AddedDate" : ViewState["GridViewSortExpression"] as string; }
            set { ViewState["GridViewSortExpression"] = value; }
        }

        public SortDirection GridViewSortOrder
        {
            get { return ViewState["GridViewSortOrder"] == null ? SortDirection.Descending : (SortDirection)ViewState["GridViewSortOrder"]; }
            set { ViewState["GridViewSortOrder"] = value; }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                CheckPermission();
                LoadDDL();
                LoadReport();
            }
        }

        public void CheckPermission()
        {
            MembershipUser someUser = Membership.GetUser();
            if (someUser == null)
            {
                Response.Redirect(Globals.ApplicationPath + "LoginAdmin/Default.aspx");
            }
            bool flag = false;
            var user = entity.aspnet_Users.Where(us => us.UserName == someUser.UserName).FirstOrDefault();
            user.aspnet_Roles.Load();
            var rolesForUser = user.aspnet_Roles.ToList();
            foreach (var item in rolesForUser)
            {
                var permission = entity.udv_Permissions.Where(ps => ps.RoleId == item.RoleId).FirstOrDefault();
                if (permission.Report.Value == true)
                {
                    flag = true;
                }
            }
            if (flag == false)
            {
                lblPermissionMessage.Visible = true;
                Panel1.Visible = false;
            }
        }

        public void LoadDDL()
        {
            ddlTypePost.Items.Add(new ListItem("Bài viết", ((int)TypePost.Article).ToString()));
            ddlTypePost.Items.Add(new ListItem("Sản phẩm", ((int)TypePost.Product).ToString()));
            ddlTypePost.Items.Add(new ListItem("Hình ảnh", ((int)TypePost.Photo).ToString()));
            ddlTypePost.Items.Add(new ListItem("Video clip", ((int)TypePost.Video).ToString()));

            LoadCateDDL();
        }

        public void LoadCateDDL()
        {
            string levels = "|---";
            string spaces = "&nbsp;";
            List<udv_Categories> allCate = entity.udv_Categories.ToList();
            List<udv_Categories> cateBannerParent = allCate.Where(ca => ca.ParentID == 0).ToList();
            foreach (var item in cateBannerParent)
            {
                ddlCategories.Items.Add(new ListItem(item.Title, item.CateID.ToString()));
                LoadSubCateDDL(ref allCate, item.CateID, ref spaces, levels);
            }
        }

        public void LoadSubCateDDL(ref List<udv_Categories> allCate, int cateParentID, ref string spaces, string levels)
        {
            List<udv_Categories> subCateBanner = allCate.Where(ca => ca.ParentID == cateParentID).ToList();
            foreach (var item in subCateBanner)
            {
                ddlCategories.Items.Add(new ListItem(HttpUtility.HtmlDecode(spaces) + levels + item.Title, item.CateID.ToString()));
                if (entity.udv_Categories.Where(ca => ca.ParentID == item.CateID).ToList().Count > 0)
                {
                    spaces += "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
                    LoadSubCateDDL(ref allCate, item.CateID, ref spaces, levels);
                }
            }
            if (spaces.Length > 6)
                spaces = spaces.Substring(0, spaces.Length - 36);
        }

        public void LoadReport()
        {
            DateTime timeTemp = DateTime.Now.AddMonths(3);
            List<udv_Report> reports = entity.udv_Articles.Include("udv_Categories").AsEnumerable().Select(ar => new udv_Report
            {
                Title = ar.Title,
                Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(ar.UrlRewrite, DGSFramework.Globals.GetUrlAspx(ar.UrlRewrite, false) + ar.ArticleID),
                CateID = ar.udv_Categories.CateID,
                CateTitle = ar.udv_Categories.Title,
                AddedBy = ar.AddedBy,
                AddedDate = ar.AddedDate,
                TypePost = (int)TypePost.Article
            }).Where(ar => ar.AddedDate <= timeTemp).ToList();

            List<udv_Report> reportsProduct = entity.udv_Products.Include("udv_Categories").AsEnumerable().Select(pr => new udv_Report
            {
                Title = pr.Title,
                Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(pr.UrlRewrite, DGSFramework.Globals.GetUrlAspx(pr.UrlRewrite, false) + pr.ProductID),
                CateID = pr.udv_Categories.CateID,
                CateTitle = pr.udv_Categories.Title,
                AddedBy = pr.AddedBy,
                AddedDate = pr.AddedDate,
                TypePost = (int)TypePost.Product
            }).Where(pr => pr.AddedDate <= timeTemp).ToList();

            List<udv_Report> reportsPhoto = entity.udv_Photos.Include("udv_Categories").AsEnumerable().Select(pt => new udv_Report
            {
                Title = pt.Title,
                Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(pt.UrlRewrite, DGSFramework.Globals.GetUrlAspx(pt.UrlRewrite, false) + pt.PhotoID),
                CateID = pt.udv_Categories.CateID,
                CateTitle = pt.udv_Categories.Title,
                AddedBy = pt.AddedBy,
                AddedDate = pt.AddedDate.Value,
                TypePost = (int)TypePost.Photo
            }).Where(pt => pt.AddedDate <= timeTemp).ToList();

            List<udv_Report> reportsVideo = entity.udv_Videos.Include("udv_Categories").AsEnumerable().Select(vd => new udv_Report
            {
                Title = vd.Title,
                Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(vd.UrlRewrite, DGSFramework.Globals.GetUrlAspx(vd.UrlRewrite, false) + vd.VideoID),
                CateID = vd.udv_Categories.CateID,
                CateTitle = vd.udv_Categories.Title,
                AddedBy = vd.AddedBy,
                AddedDate = vd.AddedDate.Value,
                TypePost = (int)TypePost.Video
            }).Where(vd => vd.AddedDate <= timeTemp).ToList();

            reports.AddRange(reportsProduct);
            reports.AddRange(reportsPhoto);
            reports.AddRange(reportsVideo);

            //load user post news
            var user = reports.GroupBy(rp => rp.AddedBy).Select(rp => rp.Key).ToList();
            ddlUsers.DataSource = user;
            ddlUsers.DataBind();

            reports = reports.OrderByDescending(rp => rp.AddedDate).ToList();

            grvReports.DataSource = reports;
            grvReports.DataBind();
        }

        protected void grvReports_Sorting(object sender, GridViewSortEventArgs e)
        {
            //Check if the same column is sorted to switch Sort Order  
            if (GridViewSortExpression.Equals(e.SortExpression))
                GridViewSortOrder = GridViewSortOrder.Equals(SortDirection.Ascending) ? SortDirection.Descending : SortDirection.Ascending;
            else
                GridViewSortOrder = SortDirection.Ascending;
            //save Sort Expression and Sort Order
            GridViewSortExpression = e.SortExpression;
            if (grvReports.ToolTip == "all")
                LoadReport();
            else
            {
                SearchReport();
            }
        }

        protected void grvReports_DataBinding(object sender, EventArgs e)
        {
            string srcImageASC = Globals.ApplicationPath + "WebmasterTool/Images/ASC.gif";
            string srcImageDESC = Globals.ApplicationPath + "WebmasterTool/Images/DESC.gif";
            foreach (DataControlField column in grvReports.Columns)
            {
                //Remove Previous Sort Image  
                column.HeaderText = Regex.Replace(column.HeaderText, "<[^>]*>", "", RegexOptions.IgnoreCase);
                if (column.SortExpression.Equals(GridViewSortExpression))
                {
                    //set new sort image  
                    string strSortImageURL = GridViewSortOrder == SortDirection.Descending ?
                                            "<img border='0' style='background-color:White;vertical-align:middle;' src='" + srcImageDESC + "' />" :
                                            "<img border='0' style='background-color:White;vertical-align:middle;' src='" + srcImageASC + "' />";
                    column.HeaderText = String.Format("{0}{1}", column.HeaderText, strSortImageURL);
                }
            }
        }

        protected void grvReports_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            grvReports.PageIndex = e.NewPageIndex;
            if (grvReports.ToolTip == "all")
                LoadReport();
            else
            {
                SearchReport();
            }
        }

        public void SearchReport()
        {
            List<udv_Report> reports = LoadSearchReport();

            grvReports.DataSource = reports;
            grvReports.DataBind();
        }

        private List<udv_Report> LoadSearchReport()
        {
            List<udv_Report> reports = new List<udv_Report>();
            reports = LoadSearchReportTypePost(reports);

            if (ddlUsers.SelectedValue != "0")
            {
                reports = reports.Where(rp => rp.AddedBy == ddlUsers.SelectedValue).ToList();
            }
            if (ddlCategories.SelectedValue != "0")
            {
                int cateID = int.Parse(ddlCategories.SelectedValue);
                var allCate = entity.udv_Categories.ToList();
                List<udv_Categories> lstCateByCateID = Globals.LoopGetCategories(ref allCate, cateID);
                lstCateByCateID.Add(allCate.Where(ca => ca.CateID == cateID).FirstOrDefault());
                List<int> lstCateID = lstCateByCateID.Select(ca => ca.CateID).ToList();

                reports = reports.Where(rp => lstCateID.Contains(rp.CateID)).ToList();
            }
            if (!string.IsNullOrEmpty(txtFomDate.Text.Trim()))
            {
                DateTime dateFrom = DateTime.Parse(txtFomDate.Text.Trim());
                reports = reports.Where(rp => rp.AddedDate >= dateFrom).ToList();
            }
            if (!string.IsNullOrEmpty(txtToDate.Text.Trim()))
            {
                DateTime dateTo = DateTime.Parse(txtToDate.Text.Trim());
                reports = reports.Where(rp => rp.AddedDate <= dateTo).ToList();
            }

            if (GridViewSortOrder.Equals(SortDirection.Ascending))
                reports = reports.OrderBy(a => a.GetType().GetProperty(GridViewSortExpression).GetValue(a, null)).ToList();
            else
                reports = reports.OrderByDescending(a => a.GetType().GetProperty(GridViewSortExpression).GetValue(a, null)).ToList();

            return reports;
        }

        private List<udv_Report> LoadSearchReportTypePost(List<udv_Report> reports)
        {
            DateTime timeTemp = DateTime.Now.AddMonths(3);
            if (ddlTypePost.SelectedValue == "0")
            {
                reports = entity.udv_Articles.Include("udv_Categories").AsEnumerable().Select(ar => new udv_Report
                {
                    Title = ar.Title,
                    Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(ar.UrlRewrite, DGSFramework.Globals.GetUrlAspx(ar.UrlRewrite, false) + ar.ArticleID),
                    CateID = ar.udv_Categories.CateID,
                    CateTitle = ar.udv_Categories.Title,
                    AddedBy = ar.AddedBy,
                    AddedDate = ar.AddedDate,
                    TypePost = (int)TypePost.Article
                }).Where(ar => ar.AddedDate <= timeTemp).ToList();

                List<udv_Report> reportsProduct = entity.udv_Products.Include("udv_Categories").AsEnumerable().Select(pr => new udv_Report
                {
                    Title = pr.Title,
                    Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(pr.UrlRewrite, DGSFramework.Globals.GetUrlAspx(pr.UrlRewrite, false) + pr.ProductID),
                    CateID = pr.udv_Categories.CateID,
                    CateTitle = pr.udv_Categories.Title,
                    AddedBy = pr.AddedBy,
                    AddedDate = pr.AddedDate,
                    TypePost = (int)TypePost.Product
                }).Where(pr => pr.AddedDate <= timeTemp).ToList();

                List<udv_Report> reportsPhoto = entity.udv_Photos.Include("udv_Categories").AsEnumerable().Select(pt => new udv_Report
                {
                    Title = pt.Title,
                    Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(pt.UrlRewrite, DGSFramework.Globals.GetUrlAspx(pt.UrlRewrite, false) + pt.PhotoID),
                    CateID = pt.udv_Categories.CateID,
                    CateTitle = pt.udv_Categories.Title,
                    AddedBy = pt.AddedBy,
                    AddedDate = pt.AddedDate.Value,
                    TypePost = (int)TypePost.Photo
                }).Where(pt => pt.AddedDate <= timeTemp).ToList();

                List<udv_Report> reportsVideo = entity.udv_Videos.Include("udv_Categories").AsEnumerable().Select(vd => new udv_Report
                {
                    Title = vd.Title,
                    Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(vd.UrlRewrite, DGSFramework.Globals.GetUrlAspx(vd.UrlRewrite, false) + vd.VideoID),
                    CateID = vd.udv_Categories.CateID,
                    CateTitle = vd.udv_Categories.Title,
                    AddedBy = vd.AddedBy,
                    AddedDate = vd.AddedDate.Value,
                    TypePost = (int)TypePost.Video
                }).Where(vd => vd.AddedDate <= timeTemp).ToList();

                reports.AddRange(reportsProduct);
                reports.AddRange(reportsPhoto);
                reports.AddRange(reportsVideo);
            }
            else if (ddlTypePost.SelectedValue == "1")
            {
                reports = entity.udv_Articles.Include("udv_Categories").AsEnumerable().Select(ar => new udv_Report
                {
                    Title = ar.Title,
                    Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(ar.UrlRewrite, DGSFramework.Globals.GetUrlAspx(ar.UrlRewrite, false) + ar.ArticleID),
                    CateID = ar.udv_Categories.CateID,
                    CateTitle = ar.udv_Categories.Title,
                    AddedBy = ar.AddedBy,
                    AddedDate = ar.AddedDate,
                    TypePost = (int)TypePost.Article
                }).Where(ar => ar.AddedDate <= timeTemp).ToList();
            }
            else if (ddlTypePost.SelectedValue == "2")
            {
                reports = entity.udv_Products.Include("udv_Categories").AsEnumerable().Select(pr => new udv_Report
                {
                    Title = pr.Title,
                    Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(pr.UrlRewrite, DGSFramework.Globals.GetUrlAspx(pr.UrlRewrite, false) + pr.ProductID),
                    CateID = pr.udv_Categories.CateID,
                    CateTitle = pr.udv_Categories.Title,
                    AddedBy = pr.AddedBy,
                    AddedDate = pr.AddedDate,
                    TypePost = (int)TypePost.Product
                }).Where(pr => pr.AddedDate <= timeTemp).ToList();
            }
            else if (ddlTypePost.SelectedValue == "3")
            {
                reports = entity.udv_Photos.Include("udv_Categories").AsEnumerable().Select(pt => new udv_Report
                {
                    Title = pt.Title,
                    Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(pt.UrlRewrite, DGSFramework.Globals.GetUrlAspx(pt.UrlRewrite, false) + pt.PhotoID),
                    CateID = pt.udv_Categories.CateID,
                    CateTitle = pt.udv_Categories.Title,
                    AddedBy = pt.AddedBy,
                    AddedDate = pt.AddedDate.Value,
                    TypePost = (int)TypePost.Photo
                }).Where(pt => pt.AddedDate <= timeTemp).ToList();
            }
            else
            {
                reports = entity.udv_Videos.Include("udv_Categories").AsEnumerable().Select(vd => new udv_Report
                {
                    Title = vd.Title,
                    Link = ConfigPaths.Domain_Url + DGSFramework.Globals.GetUrl(vd.UrlRewrite, DGSFramework.Globals.GetUrlAspx(vd.UrlRewrite, false) + vd.VideoID),
                    CateID = vd.udv_Categories.CateID,
                    CateTitle = vd.udv_Categories.Title,
                    AddedBy = vd.AddedBy,
                    AddedDate = vd.AddedDate.Value,
                    TypePost = (int)TypePost.Video
                }).Where(vd => vd.AddedDate <= timeTemp).ToList();
            }
            return reports;
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            grvReports.PageIndex = 0;
            grvReports.ToolTip = "search";
            SearchReport();
        }

        protected void btnExportExcel_Click(object sender, EventArgs e)
        {
            List<udv_Report> report = LoadSearchReport();

            //Create new Excel Workbook
            var workbook = new HSSFWorkbook();
            //Create new Excel Sheet
            var sheet = workbook.CreateSheet();
            //(Optional) set the width of the columns
            sheet.SetColumnWidth(0, 10 * 256);//so thu tu
            sheet.SetColumnWidth(1, 30 * 256);//ten bai viet
            sheet.SetColumnWidth(2, 20 * 256);//thoi gian xuat ban
            sheet.SetColumnWidth(3, 20 * 256);//danh muc
            sheet.SetColumnWidth(4, 50 * 256);//link
            sheet.SetColumnWidth(5, 20 * 256);//nguoi xuat ban
            sheet.SetColumnWidth(6, 30 * 256);//nhan xet
            sheet.SetColumnWidth(7, 10 * 256);//diem
            sheet.SetColumnWidth(8, 20 * 256);//nhuan but
            sheet.SetColumnWidth(9, 10 * 256);//thuong
            sheet.SetColumnWidth(10, 10 * 256);//phat

            // Create the header row cell style: bold
            var style1 = workbook.CreateCellStyle();
            // cell background
            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
            // font color
            var font1 = workbook.CreateFont();
            font1.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
            font1.Boldweight = (short)FontBoldWeight.Bold;
            style1.SetFont(font1);

            // Create the header row cell style: bottom
            var style2 = workbook.CreateCellStyle();
            // cell background
            style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
            style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
            style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            // font color
            var font2 = workbook.CreateFont();
            font2.Underline = FontUnderlineType.Single;
            style2.SetFont(font2);

            // Create the header row cell style: all row
            var style3 = workbook.CreateCellStyle();
            // cell background
            style3.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
            style3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style3.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            //Create a header row 1
            var headerRow1 = sheet.CreateRow(0);
            headerRow1.CreateCell(4).SetCellValue("BÁO CÁO - THỐNG KÊ TIN BÀI - NHUẬN BÚT");
            headerRow1.Cells[0].CellStyle = style1;
            //Create a header row 2
            var headerRow2 = sheet.CreateRow(1);
            headerRow2.CreateCell(1).SetCellValue("Xuất báo cáo lúc: " + string.Format("{0:dd/MM/yyyy HH:mm:ss}", DateTime.Now));
            //Create a header row 3
            var headerRow3 = sheet.CreateRow(3);
            headerRow3.CreateCell(0).SetCellValue("STT");
            headerRow3.CreateCell(1).SetCellValue("TÊN BÀI VIẾT");
            headerRow3.CreateCell(2).SetCellValue("THỜI GIAN XUẤT BẢN");
            headerRow3.CreateCell(3).SetCellValue("DANH MỤC");
            headerRow3.CreateCell(4).SetCellValue("LINK");
            headerRow3.CreateCell(5).SetCellValue("NGƯỜI XUẤT BẢN");
            headerRow3.CreateCell(6).SetCellValue("NHẬN XÉT");
            headerRow3.CreateCell(7).SetCellValue("ĐIỂM");
            headerRow3.CreateCell(8).SetCellValue("NHUẬN BÚT");
            headerRow3.CreateCell(9).SetCellValue("THƯỞNG");
            headerRow3.CreateCell(10).SetCellValue("PHẠT");
            headerRow3.Cells.ForEach(cs => cs.CellStyle = style2);

            int index = 1;
            int rowNumber = 4;
            //Populate the sheet with values from the grid data
            foreach (var item in report)
            {
                //Create a new Row
                var row = sheet.CreateRow(rowNumber++);
                //Set the Values for Cells
                row.CreateCell(0).SetCellValue(index++);
                row.CreateCell(1).SetCellValue(item.Title);
                row.CreateCell(2).SetCellValue(string.Format("{0:dd/MM/yyyy HH:mm:ss}", item.AddedDate));
                row.CreateCell(3).SetCellValue(item.CateTitle);
                row.CreateCell(4).SetCellValue(item.Link);
                row.CreateCell(5).SetCellValue(item.AddedBy);
                row.CreateCell(6).SetCellValue("");
                row.CreateCell(7).SetCellValue("");
                row.CreateCell(8).SetCellValue("");
                row.CreateCell(9).SetCellValue("");
                row.CreateCell(10).SetCellValue("");

                row.Cells.ForEach(cs => cs.CellStyle = style3);
            }
            //Write the Workbook to a memory stream
            MemoryStream output = new MemoryStream();
            workbook.Write(output);
            //Return the result to the end user
            string saveAsFileName = string.Format("BaoCao_{0}.xls", string.Format("{0:dd/MM/yyyy}", DateTime.Now));

            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
            Response.Clear();
            Response.BinaryWrite(output.ToArray());
            Response.End();
        }

        public class udv_Report
        {
            public string Title { get; set; }
            public string Link { get; set; }
            public int CateID { get; set; }
            public string CateTitle { get; set; }
            public string AddedBy { get; set; }
            public DateTime AddedDate { get; set; }
            public int TypePost { get; set; }
        }
    }
}